/***********************************************************************************************************************************
 * Name:	Backup_History.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Show the backup history of the current database.
	SELECT * FROM msdb.dbo.backupmediaset			-- media_set_id									RESTORE LABELONLY
		SELECT * FROM msdb.dbo.backupmediafamily	-- media_set_id+family_sequence_number+mirror	RESTORE LABELONLY
		SELECT * FROM msdb.dbo.backupset			-- backup_set_id  (media_set_id)				RESTORE HEADERONLY
			SELECT * FROM msdb.dbo.backupfile		-- backup_set_id+file_number					RESTORE FILELISTONLY
			SELECT * FROM msdb.dbo.backupfilegroup	-- backup_set_id+filegroup_id
 */
SELECT
	b.backup_set_id,
	b.database_name,
	b.type,
	b.is_copy_only,
	b.backup_finish_date,
	duration= DATEDIFF(mi,b.backup_start_date,b.backup_finish_date),
	backup_mb= CAST(ROUND(b.backup_size/1024/1024,2) AS FLOAT),
	b.name,
	b.description,
--	m.media_set_id,
--	m.family_sequence_number,
	device_type= CASE m.device_type WHEN 2 THEN N'Disk' WHEN 5 THEN N'Tape' WHEN 7 THEN N'Virtual' ELSE CAST(m.device_type AS NVARCHAR(7)) END,
	m.physical_device_name,
	b.user_name
  FROM msdb.dbo.backupset b -- the What
   INNER JOIN msdb.dbo.backupmediafamily m ON (b.media_set_id = m.media_set_id) -- the Where
  WHERE b.is_copy_only = 0 AND b.type IN ('D','I') AND b.database_name = DB_NAME()
  ORDER BY b.database_name, b.backup_finish_date DESC, m.family_sequence_number;
GO